package fact.view.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.annotation.Resource;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.RequestScoped;
import javax.sql.DataSource;

import fact.view.DTO.NavDTO;
import fact.view.DTO.NodeDTO;
import fact.view.DTO.SegmentDTO;


@ManagedBean(name = "nodeModelBean")
@RequestScoped
public class TreeNodeDAO {
	
	@Resource(name="jdbc/factentry")
	private DataSource ds;
	
	public ArrayList<NavDTO> getLeftNavNodeList(String userId) throws SQLException
	{
		ArrayList<NavDTO> nodeList=new ArrayList<NavDTO>();
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");

		PreparedStatement ps = con.prepareStatement("Select brand.MAKE_ID,brand.MAKE_NAME,market.MARKET_ID,market.MARKET_NAME, " +
							   " r.role_id from RESPONSIBILITIES r,MST_MAKES brand , MST_MARKETS market Where user_id=? " +
							   " And r.MAKE_ID=brand.MAKE_ID and r.MARKET_ID=market.MARKET_ID " +
							   " order by brand.MAKE_ID,market.MARKET_ID");
		
		ps.setString(1, userId);
		ResultSet result =  ps.executeQuery();
		
				while(result.next())
				{
					NavDTO n=new NavDTO();
					n.setMakeId(result.getInt("MAKE_ID"));
					n.setMakeName(result.getString("MAKE_NAME"));
					n.setMarketId(result.getInt("MARKET_ID"));
					n.setMarketName(result.getString("MARKET_NAME"));
					nodeList.add(n);
			
				}
		return nodeList;
	}
	
	public ArrayList<SegmentDTO> getSegmentList() throws SQLException
	{
		ArrayList<SegmentDTO> segmentList=new ArrayList<SegmentDTO>();
		if(ds==null)
			throw new SQLException("Can't get data source");
		Connection con = ds.getConnection();
 
		if(con==null)
			throw new SQLException("Can't get database connection");

		PreparedStatement ps = con.prepareStatement("Select * from MST_SEGMENTS order by Position");
		ResultSet result =  ps.executeQuery();
 		
				while(result.next())
				{
					SegmentDTO s=new SegmentDTO();
					s.setSegmentId(result.getInt("SEGMENT_ID"));
					s.setSegmentName(result.getString("SEGMENT_NAME"));
					segmentList.add(s);
				
			
		}
		return segmentList;
	}


}
